package com.weixin1.dao;

import com.weixin1.bean.News;
import com.weixin1.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class NewsDao {
    private QueryRunner qr = new QueryRunner(C3p0Utils.comboPooledDataSource);
    public List<News> list() throws SQLException {
        return  qr.query("select * from news", new BeanListHandler<>(News.class));
    }


    public News getById(Long id) throws SQLException {
        return qr.query("select * from news where nid=?",new BeanHandler<>(News.class),id);
    }

    public int save(News news)throws SQLException{
     return qr.update("insert into news (title,author,content,summary,path,pname,time) values(?,?,?,?,?,?,?)",news.getTitle(),news.getAuthor(),
             news.getContent(),news.getSummary(),news.getPath(),news.getPname(),news.getTime());
    }

    public int removeById(Long id)throws SQLException {
        return qr.update("delete from news where nId=?",id);
    }

    public int update(News news)throws SQLException{
        return qr.update("update news set title=?,author=?,content=?,summary=?,path=?,time=now() where nId=?",
                news.getTitle(),news.getAuthor(),news.getContent(),news.getSummary(),news.getPath(),news.getNId());
    }
    //分页查询
    public List<News> selectByPage(int page, int limit) throws SQLException {
        return qr.query("select * from news " +
                "limit ?,?",new BeanListHandler<>(News.class),(page-1)*limit,limit);
    }
    //获取总数据条数
    public int gettotal() throws SQLException {
        return qr.query("select count(*) from news ",new ScalarHandler<Long>()).intValue();
    }
}
